package com.yupi.springbootinit.utils.excel;

import com.yupi.springbootinit.common.ErrorCode;
import com.yupi.springbootinit.exception.BusinessException;
import com.yupi.springbootinit.model.entity.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 质检报告 Excel 导出工具类
 *
 * @author Administrator
 * @date 2025-01-06
 */
@Slf4j
public class QaInspectionReportExcelExporter {

    /**
     * 导出检验报告为 Excel 字节数组
     *
     * @param report       报告主表数据
     * @param dimMap       尺寸数据 Map（key: FAI1-FAI10）
     * @param instruments  仪器设备列表
     * @param logoBytes    Logo 图片字节数组
     * @param imageBytes   产品图片字节数组
     * @return Excel 文件字节数组
     */
    public static byte[] exportToExcel(QaInspectionReport report,
                                       Map<String, QaInspectionReportDim> dimMap,
                                       List<QaInspectionInstrument> instruments,
                                       byte[] logoBytes,
                                       byte[] imageBytes) {
        log.info("========== 开始生成检验报告Excel ==========");

        try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            Sheet sheet = wb.createSheet("成品检验报告");

            // 页面设置
            setupPageSettings(sheet);

            // 列宽设置
            setupColumnWidths(sheet);

            // 创建字体
            ExcelFonts fonts = createFonts(wb);

            // 创建样式
            ExcelStyles styles = createStyles(wb, fonts);

            // 当前行号
            int currentRow = 0;

            // 第1行：留空（带下框线）
            currentRow = createBlankRow(sheet, currentRow, styles);

            // 第2-4行：Logo + 公司名称
            currentRow = createHeaderSection(sheet, wb, currentRow, styles, logoBytes);

            // 第5-6行：标题
            currentRow = createTitleSection(sheet, currentRow, styles);

            // 第7-8行：基础信息
            currentRow = createBasicInfoSection(sheet, currentRow, report, styles);

            // 第9-12行：检验仪器设备
            currentRow = createInstrumentSection(sheet, currentRow, instruments, styles);

            // 第13-23行：主要尺寸检测 + 图片
            currentRow = createDimensionSection(sheet, wb, currentRow, report, dimMap, imageBytes, styles);

            // 第24-26行：外观与检测内容
            currentRow = createAppearanceSection(sheet, currentRow, report, styles);

            // 第27-29行：判定结果
            currentRow = createJudgementSection(sheet, currentRow, report, styles);

            // 第30行：签名栏
            currentRow = createSignatureSection(sheet, currentRow, styles);

            // 第31行：底部留白行
            currentRow = createBottomBlankRow(sheet, currentRow, styles);

            wb.write(baos);
            log.info("✅ Excel生成成功，总行数：{}", currentRow);
            return baos.toByteArray();
        } catch (IOException e) {
            log.error("❌ 生成Excel失败", e);
            throw new BusinessException(ErrorCode.OPERATION_ERROR, "导出失败：" + e.getMessage());
        }
    }


    // ==================== 页面设置 ====================

    private static void setupPageSettings(Sheet sheet) {
        sheet.setFitToPage(true);
        sheet.getPrintSetup().setLandscape(false);
        sheet.setMargin(Sheet.TopMargin, 0.75);
        sheet.setMargin(Sheet.BottomMargin, 0.75);
        sheet.setMargin(Sheet.LeftMargin, 0.7);
        sheet.setMargin(Sheet.RightMargin, 0.7);
    }

    private static void setupColumnWidths(Sheet sheet) {
        for (int i = 0; i < 9; i++) {
            sheet.setColumnWidth(i, 256 * 12);
        }
    }

    // ==================== 字体创建 ====================

    private static ExcelFonts createFonts(Workbook wb) {
        ExcelFonts fonts = new ExcelFonts();

        // 公司名称：宋体加粗 16号
        fonts.companyFont = wb.createFont();
        fonts.companyFont.setFontName("宋体");
        fonts.companyFont.setBold(true);
        fonts.companyFont.setFontHeightInPoints((short) 16);

        // 主标题：宋体加粗 18号
        fonts.titleFont = wb.createFont();
        fonts.titleFont.setFontName("宋体");
        fonts.titleFont.setBold(true);
        fonts.titleFont.setFontHeightInPoints((short) 18);

        // 副标题：宋体加粗 12号
        fonts.subtitleFont = wb.createFont();
        fonts.subtitleFont.setFontName("宋体");
        fonts.subtitleFont.setBold(true);
        fonts.subtitleFont.setFontHeightInPoints((short) 12);

        // 表头：宋体加粗 11号
        fonts.headerFont = wb.createFont();
        fonts.headerFont.setFontName("宋体");
        fonts.headerFont.setBold(true);
        fonts.headerFont.setFontHeightInPoints((short) 11);

        // 判定结果字体：宋体加粗 12号
        fonts.judgementFont = wb.createFont();
        fonts.judgementFont.setFontName("宋体");
        fonts.judgementFont.setBold(true);
        fonts.judgementFont.setFontHeightInPoints((short) 12);

        // 注释字体：宋体加粗 11号
        fonts.noteFont = wb.createFont();
        fonts.noteFont.setFontName("宋体");
        fonts.noteFont.setBold(true);
        fonts.noteFont.setFontHeightInPoints((short) 11);

        // 正文：宋体 10号
        fonts.normalFont = wb.createFont();
        fonts.normalFont.setFontName("宋体");
        fonts.normalFont.setFontHeightInPoints((short) 10);

        return fonts;
    }

    // ==================== 样式创建 ====================

    private static ExcelStyles createStyles(Workbook wb, ExcelFonts fonts) {
        ExcelStyles styles = new ExcelStyles();

        // 公司名称样式（居中）
        styles.companyStyle = wb.createCellStyle();
        styles.companyStyle.setFont(fonts.companyFont);
        styles.companyStyle.setAlignment(HorizontalAlignment.CENTER);
        styles.companyStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // ✅ 新增：公司名称样式（左对齐）
        styles.companyStyleLeft = wb.createCellStyle();
        styles.companyStyleLeft.setFont(fonts.companyFont);
        styles.companyStyleLeft.setAlignment(HorizontalAlignment.LEFT);
        styles.companyStyleLeft.setVerticalAlignment(VerticalAlignment.CENTER);

        // 主标题样式
        styles.titleStyle = wb.createCellStyle();
        styles.titleStyle.setFont(fonts.titleFont);
        styles.titleStyle.setAlignment(HorizontalAlignment.CENTER);
        styles.titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 副标题样式
        styles.subtitleStyle = wb.createCellStyle();
        styles.subtitleStyle.setFont(fonts.subtitleFont);
        styles.subtitleStyle.setAlignment(HorizontalAlignment.CENTER);
        styles.subtitleStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 表头样式
        styles.headerStyle = createBorderedStyle(wb, fonts.headerFont, HorizontalAlignment.CENTER);

        styles.headerStyleWrap = createBorderedStyle(wb, fonts.headerFont, HorizontalAlignment.CENTER);
        styles.headerStyleWrap.setWrapText(true);

        // 判定结果样式
        styles.judgementStyle = createBorderedStyle(wb, fonts.judgementFont, HorizontalAlignment.CENTER);

        // 注释样式
        styles.noteStyle = createBorderedStyle(wb, fonts.noteFont, HorizontalAlignment.LEFT);

        // 最终判定样式
        styles.finalJudgeStyle = createBorderedStyle(wb, fonts.judgementFont, HorizontalAlignment.LEFT);

        // 正文样式
        styles.normalStyle = createBorderedStyle(wb, fonts.normalFont, HorizontalAlignment.CENTER);

        // 左对齐样式
        styles.leftStyle = createBorderedStyle(wb, fonts.normalFont, HorizontalAlignment.LEFT);

        // 自动换行样式
        styles.wrapStyle = createBorderedStyle(wb, fonts.normalFont, HorizontalAlignment.LEFT);
        styles.wrapStyle.setWrapText(true);

        // 竖排文字样式
        styles.verticalStyle = createBorderedStyle(wb, fonts.headerFont, HorizontalAlignment.CENTER);
        styles.verticalStyle.setWrapText(true);

        return styles;
    }

    private static CellStyle createBorderedStyle(Workbook wb, Font font, HorizontalAlignment align) {
        CellStyle style = wb.createCellStyle();
        style.setFont(font);
        style.setAlignment(align);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    // ==================== 各区域创建方法 ====================

    /**
     * 第1行：留空
     */
    /**
     * 第1行：留空（合并A-I列，带下框线）
     */
    private static int createBlankRow(Sheet sheet, int currentRow, ExcelStyles styles) {
        Row blankRow = getOrCreateRow(sheet, currentRow);
        blankRow.setHeightInPoints(15);

        // 合并A-I列
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, 8));

        // 创建只有下边框的样式
        CellStyle bottomBorderStyle = sheet.getWorkbook().createCellStyle();
        bottomBorderStyle.setBorderBottom(BorderStyle.THIN);
        bottomBorderStyle.setAlignment(HorizontalAlignment.CENTER);
        bottomBorderStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 应用样式到所有单元格
        for (int c = 0; c <= 8; c++) {
            Cell cell = getOrCreateCell(blankRow, c);
            cell.setCellStyle(bottomBorderStyle);
        }

        return currentRow + 1;
    }

    /**
     * 第2-4行：Logo + 公司名称
     */
    private static int createHeaderSection(Sheet sheet, Workbook wb, int currentRow,
                                           ExcelStyles styles, byte[] logoBytes) {
        // 第2-4行A列合并放Logo
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));

        // 第2-3行B-I列合并放公司名称
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 8));

        // 创建行并设置行高
        Row row2 = getOrCreateRow(sheet, 1);
        row2.setHeightInPoints(25f);

        Row row3 = getOrCreateRow(sheet, 2);
        row3.setHeightInPoints(15f);

        Row row4 = getOrCreateRow(sheet, 3);
        row4.setHeightInPoints(15f);

        // 第2行：Logo区域 + 公司名称
        Cell logoCell2 = getOrCreateCell(row2, 0);
        logoCell2.setCellStyle(styles.companyStyle);

        Cell companyCell = getOrCreateCell(row2, 1);
        // ✅ 修改：左对齐 + 17个空格
        companyCell.setCellValue("                 四川五八增材科技有限公司");
        companyCell.setCellStyle(styles.companyStyleLeft); // 使用新的左对齐样式
        for (int c = 2; c <= 8; c++) {
            getOrCreateCell(row2, c).setCellStyle(styles.companyStyleLeft);
        }

        // 第3行
        Cell logoCell3 = getOrCreateCell(row3, 0);
        logoCell3.setCellStyle(styles.companyStyle);
        for (int c = 1; c <= 8; c++) {
            getOrCreateCell(row3, c).setCellStyle(styles.companyStyleLeft);
        }

        // 第4行
        Cell logoCell4 = getOrCreateCell(row4, 0);
        logoCell4.setCellStyle(styles.companyStyle);
        for (int c = 1; c <= 8; c++) {
            getOrCreateCell(row4, c).setCellStyle(styles.companyStyleLeft);
        }

        // 插入Logo图片
        if (logoBytes != null) {
            insertLogo(sheet, wb, logoBytes);
        }

        return 4;
    }

    /**
     * 插入Logo图片
     */
    private static void insertLogo(Sheet sheet, Workbook wb, byte[] logoBytes) {
        try {
            int logoIdx = wb.addPicture(logoBytes, Workbook.PICTURE_TYPE_PNG);
            Drawing<?> drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = new XSSFClientAnchor();
            anchor.setCol1(0);
            anchor.setRow1(1);
            anchor.setCol2(1);
            anchor.setRow2(4);
            anchor.setDx1(150000);
            anchor.setDy1(100000);
            anchor.setDx2(-150000);
            anchor.setDy2(-100000);
            drawing.createPicture(anchor, logoIdx);
        } catch (Exception e) {
            log.warn("插入Logo失败", e);
        }
    }

    /**
     * 第5-6行：标题
     */
    private static int createTitleSection(Sheet sheet, int currentRow, ExcelStyles styles) {
        // 第5行：成品检验报告 - 行高30
        createMergedCell(sheet, currentRow, currentRow, 0, 8, "成品检验报告", styles.titleStyle);
        sheet.getRow(currentRow).setHeightInPoints(35f);
        currentRow++;

        // 第6行：英文副标题 - 行高20
        createMergedCell(sheet, currentRow, currentRow, 0, 8,
                "Finished product inspection report", styles.subtitleStyle);
        sheet.getRow(currentRow).setHeightInPoints(25f);  // ✅ 修改：15 → 20
        currentRow++;

        return currentRow;
    }

    /**
     * 第7-8行：基础信息
     */
    private static int createBasicInfoSection(Sheet sheet, int currentRow,
                                              QaInspectionReport report, ExcelStyles styles) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        // 第7行 - 行高30
        Row info1 = getOrCreateRow(sheet, currentRow);
        info1.setHeightInPoints(35);  // ✅ 修改：22 → 30

        createInfoCell(sheet, currentRow, 0, "产品名称",
                StringUtils.defaultString(report.getProductName()),
                styles.headerStyle, styles.normalStyle, 2);

        createInfoCell(sheet, currentRow, 3, "检验日期",
                sdf.format(report.getReportDate()),
                styles.headerStyle, styles.normalStyle, 5);

        createInfoCell(sheet, currentRow, 6, "规格型号",
                StringUtils.defaultString(report.getSpecModel()),
                styles.headerStyle, styles.normalStyle, 8);

        currentRow++;

        // 第8行 - 行高45
        Row info2 = getOrCreateRow(sheet, currentRow);
        info2.setHeightInPoints(50);  // ✅ 修改：22 → 45

        // ✅ 修改：生产日期/批次号换行
        createInfoCell(sheet, currentRow, 0, "生产日期/\n批次号",
                StringUtils.defaultString(report.getLotNo()),
                styles.headerStyleWrap, styles.normalStyle, 2);  // 使用带换行的样式

        createInfoCell(sheet, currentRow, 3, "产品数量",
                report.getQuantity() == null ? "" : String.valueOf(report.getQuantity()),
                styles.headerStyle, styles.normalStyle, 5);

        createInfoCell(sheet, currentRow, 6, "报告编号",
                StringUtils.defaultString(report.getReportNo()),
                styles.headerStyle, styles.normalStyle, 8);

        currentRow++;

        return currentRow;
    }

    /**
     * 第9-12行：检验仪器设备
     */
    private static int createInstrumentSection(Sheet sheet, int currentRow,
                                               List<QaInspectionInstrument> instruments,
                                               ExcelStyles styles) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        // 标题行 - 行高30
        createMergedCell(sheet, currentRow, currentRow, 0, 8, "检验仪器设备", styles.headerStyle);
        sheet.getRow(currentRow).setHeightInPoints(30);  // ✅ 修改：25 → 30
        currentRow++;

        // 仪器名称 - 行高35
        Row nameRow = getOrCreateRow(sheet, currentRow);
        nameRow.setHeightInPoints(35);  // ✅ 修改：22 → 25
        createInstrumentRow(nameRow, "仪器名称", instruments,
                inst -> inst.getInstrumentName(), styles);
        currentRow++;

        // 仪器编号 - 行高25
        Row codeRow = getOrCreateRow(sheet, currentRow);
        codeRow.setHeightInPoints(35);  // ✅ 修改：22 → 25
        createInstrumentRow(codeRow, "仪器编号", instruments,
                inst -> inst.getInstrumentCode(), styles);
        currentRow++;

        // 有效期 - 行高25
        Row validRow = getOrCreateRow(sheet, currentRow);
        validRow.setHeightInPoints(35);  // ✅ 修改：22 → 25
        createInstrumentRow(validRow, "有效期", instruments,
                inst -> inst.getValidUntil() != null ? sdf.format(inst.getValidUntil()) : "",
                styles);
        currentRow++;

        return currentRow;
    }

    @FunctionalInterface
    interface InstrumentFieldExtractor {
        String extract(QaInspectionInstrument instrument);
    }

    private static void createInstrumentRow(Row row, String header,
                                            List<QaInspectionInstrument> instruments,
                                            InstrumentFieldExtractor extractor,
                                            ExcelStyles styles) {
        Cell headerCell = getOrCreateCell(row, 0);
        headerCell.setCellValue(header);
        headerCell.setCellStyle(styles.headerStyle);

        for (int i = 1; i <= 6; i++) {
            Cell cell = getOrCreateCell(row, i);
            String val = (instruments.size() >= i && instruments.get(i - 1) != null)
                    ? extractor.extract(instruments.get(i - 1)) : "";
            cell.setCellValue(val);
            cell.setCellStyle(styles.normalStyle);
        }

        for (int c = 7; c <= 8; c++) {
            getOrCreateCell(row, c).setCellStyle(styles.normalStyle);
        }
    }

    /**
     * 第13-23行：主要尺寸检测 + 图片
     */
    private static int createDimensionSection(Sheet sheet, Workbook wb, int currentRow,
                                              QaInspectionReport report,
                                              Map<String, QaInspectionReportDim> dimMap,
                                              byte[] imageBytes, ExcelStyles styles) {
        // ========== 合并区域设置 ==========

        // A列合并（第13-23行，索引12-22）
        sheet.addMergedRegion(new CellRangeAddress(12, 22, 0, 0));

        // B-D列合并（第13-23行，索引12-22）
        sheet.addMergedRegion(new CellRangeAddress(12, 22, 1, 3));

        // I列合并（第14-23行，索引13-22）- 结论只需一个
        sheet.addMergedRegion(new CellRangeAddress(13, 22, 8, 8));

        // ========== 创建第13-23行并设置基础样式 ==========

        for (int r = 12; r <= 22; r++) {
            Row row = getOrCreateRow(sheet, r);
            row.setHeightInPoints(28);  // 行高23

            // A列：主要尺寸\n检测\n(mm)
            Cell cellA = getOrCreateCell(row, 0);
            if (r == 12) {
                cellA.setCellValue("主要尺寸\n检测\n(mm)");
                cellA.setCellStyle(styles.verticalStyle);
            } else {
                cellA.setCellStyle(styles.verticalStyle);
            }

            // B-D列：图片区域
            for (int c = 1; c <= 3; c++) {
                Cell cell = getOrCreateCell(row, c);
                cell.setCellStyle(styles.normalStyle);
            }
        }

        // ========== 插入产品图片 ==========

        if (imageBytes != null) {
            insertProductImage(sheet, wb, imageBytes);
        }

        // ========== 第13行：表头（索引12）==========

        Row dimHeaderRow = sheet.getRow(12);

        // E列：尺寸
        Cell dimHeader1 = getOrCreateCell(dimHeaderRow, 4);
        dimHeader1.setCellValue("尺寸");
        dimHeader1.setCellStyle(styles.headerStyle);

        // F-G列：要求（合并）
        Cell dimHeader2 = getOrCreateCell(dimHeaderRow, 5);
        dimHeader2.setCellValue("要求");
        dimHeader2.setCellStyle(styles.headerStyle);
        sheet.addMergedRegion(new CellRangeAddress(12, 12, 5, 6));
        Cell dimHeader2_2 = getOrCreateCell(dimHeaderRow, 6);
        dimHeader2_2.setCellStyle(styles.headerStyle);

        // H列：实测
        Cell dimHeader3 = getOrCreateCell(dimHeaderRow, 7);
        dimHeader3.setCellValue("实测");
        dimHeader3.setCellStyle(styles.headerStyle);

        // I列：结论
        Cell dimHeader4 = getOrCreateCell(dimHeaderRow, 8);
        dimHeader4.setCellValue("结论");
        dimHeader4.setCellStyle(styles.headerStyle);

        // ========== 第14-23行:数据(索引13-22,FAI1~FAI10)==========

        // 填充10行数据
        for (int i = 1; i <= 10; i++) {
            Row dataRow = sheet.getRow(12 + i);  // 索引13-22
            if (dataRow == null) {
                dataRow = sheet.createRow(12 + i);
                dataRow.setHeightInPoints(23);
            }

            String key = "FAI" + i;
            QaInspectionReportDim dim = dimMap.get(key);

            // E列:尺寸(FAI1-FAI10)
            Cell cellE = getOrCreateCell(dataRow, 4);
            cellE.setCellValue(key);
            cellE.setCellStyle(styles.normalStyle);

            // F-G列:要求(合并)
            Cell cellF = getOrCreateCell(dataRow, 5);
            cellF.setCellValue(dim != null ? StringUtils.defaultString(dim.getRequirement()) : "");
            cellF.setCellStyle(styles.normalStyle);
            sheet.addMergedRegion(new CellRangeAddress(12 + i, 12 + i, 5, 6));
            Cell cellG = getOrCreateCell(dataRow, 6);
            cellG.setCellStyle(styles.normalStyle);

            // H列:实测
            Cell cellH = getOrCreateCell(dataRow, 7);
            cellH.setCellValue(dim != null ? StringUtils.defaultString(dim.getMeasuredValue()) : "");
            cellH.setCellStyle(styles.normalStyle);

            // I列:结论(使用报告的 dimensionConclusion，只在第一行显示)
            Cell cellI = getOrCreateCell(dataRow, 8);
            if (i == 1) {
                // 从 report.dimensionConclusion 读取尺寸检测结论
                String dimensionConclusion = "";
                if ("PASS".equals(report.getDimensionConclusion())) {
                    dimensionConclusion = "合格";
                } else if ("FAIL".equals(report.getDimensionConclusion())) {
                    dimensionConclusion = "不合格";
                } else if ("NA".equals(report.getDimensionConclusion())) {
                    dimensionConclusion = "N/A";
                } else if ("PENDING".equals(report.getDimensionConclusion())) {
                    dimensionConclusion = "待判定";
                }
                cellI.setCellValue(dimensionConclusion);
            }
            cellI.setCellStyle(styles.normalStyle);
        }

        return 23;
    }

    /**
     * 插入产品图片
     */
    private static void insertProductImage(Sheet sheet, Workbook wb, byte[] imageBytes) {
        try {
            int picIdx = wb.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
            Drawing<?> drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = new XSSFClientAnchor();
            anchor.setCol1(1);
            anchor.setRow1(12);
            anchor.setCol2(4);
            anchor.setRow2(23);
            anchor.setDx1(100000);
            anchor.setDy1(100000);
            anchor.setDx2(-100000);
            anchor.setDy2(-100000);
            drawing.createPicture(anchor, picIdx);
        } catch (Exception e) {
            log.warn("插入产品图片失败", e);
        }
    }

    /**
     * 第24-26行：外观与检测内容
     */
    private static int createAppearanceSection(Sheet sheet, int currentRow,
                                               QaInspectionReport report,
                                               ExcelStyles styles) {
        // ========== 合并区域 ==========

        // A列合并（第24-26行）
        sheet.addMergedRegion(new CellRangeAddress(23, 25, 0, 0));

        // B列合并（第24-26行）
        sheet.addMergedRegion(new CellRangeAddress(23, 25, 1, 1));

        // ✅ I列合并（第24-26行）- 显示统一的外观检测结果
        sheet.addMergedRegion(new CellRangeAddress(23, 25, 8, 8));

        // ========== 固定检测内容（C-H列）==========

        String[] contents = {
                "表面洁净、无油污，不得有裂纹、孔洞、分层、毛刺、塌陷等影响使用的缺陷。",
                "支撑结构残留需清理干净，棱边平直无起伏，锐边倒钝。",
                "打磨面光洁平整。"
        };

        //获取外观检测结果（I列）
        String appearanceResult = "";

        // 从 report.appearanceConclusion 读取外观检测结论
        if ("PASS".equals(report.getAppearanceConclusion())) {
            appearanceResult = "合格";
        } else if ("FAIL".equals(report.getAppearanceConclusion())) {
            appearanceResult = "不合格";
        } else if ("NA".equals(report.getAppearanceConclusion())) {
            appearanceResult = "N/A";
        } else if ("PENDING".equals(report.getAppearanceConclusion())) {
            appearanceResult = "待判定";
        }

        // ========== 创建第24-26行 ==========

        for (int i = 0; i < 3; i++) {
            Row row = getOrCreateRow(sheet, 23 + i);
            row.setHeightInPoints(28);

            // A列：外观（只在第一行显示文字）
            Cell appearCell = getOrCreateCell(row, 0);
            if (i == 0) {
                appearCell.setCellValue("外观");
            }
            appearCell.setCellStyle(styles.headerStyle);

            // B列：检测内容（只在第一行显示文字）
            Cell contentTitleCell = getOrCreateCell(row, 1);
            if (i == 0) {
                contentTitleCell.setCellValue("检测内容");
            }
            contentTitleCell.setCellStyle(styles.headerStyle);

            // C-H列：具体检测内容（三行分别显示三条内容）
            Cell contentCell = getOrCreateCell(row, 2);
            contentCell.setCellValue(contents[i]);
            contentCell.setCellStyle(styles.wrapStyle);
            sheet.addMergedRegion(new CellRangeAddress(23 + i, 23 + i, 2, 7));
            for (int c = 3; c <= 7; c++) {
                Cell cell = getOrCreateCell(row, c);
                cell.setCellStyle(styles.wrapStyle);
            }

            // ✅ I列：外观检测结果（只在第一行设置值，其余行通过合并共享）
            Cell resultCell = getOrCreateCell(row, 8);
            if (i == 0) {
                resultCell.setCellValue(appearanceResult);
            }
            resultCell.setCellStyle(styles.wrapStyle);
        }

        return 26;
    }

    /**
     * 第27-29行：判定结果
     */
    private static int createJudgementSection(Sheet sheet, int currentRow,
                                              QaInspectionReport report, ExcelStyles styles) {
        // 第27行：判定结果标题 - 行高30
        createMergedCell(sheet, currentRow, currentRow, 0, 8, "判定结果", styles.judgementStyle);
        sheet.getRow(currentRow).setHeightInPoints(30);  // ✅ 修改：25 → 30
        currentRow++;

        // 最终判定内容
        String judgeContent = StringUtils.isBlank(report.getNote()) ? "产品符合，判断合格" : report.getNote();
        String finalJudgeText = "最终判定：" + judgeContent;

        // F-I列合并（第28-29行）
        sheet.addMergedRegion(new CellRangeAddress(27, 28, 5, 8));

        // 第28行
        Row note1Row = getOrCreateRow(sheet, 27);
        note1Row.setHeightInPoints(25);

        Cell note1Cell = getOrCreateCell(note1Row, 0);
        note1Cell.setCellValue("注1：本报告仅对送检样品负责。");
        note1Cell.setCellStyle(styles.noteStyle);
        sheet.addMergedRegion(new CellRangeAddress(27, 27, 0, 4));
        for (int c = 1; c <= 4; c++) {
            getOrCreateCell(note1Row, c).setCellStyle(styles.noteStyle);
        }

        Cell finalJudgeCell = getOrCreateCell(note1Row, 5);
        finalJudgeCell.setCellValue(finalJudgeText);
        finalJudgeCell.setCellStyle(styles.finalJudgeStyle);
        for (int c = 6; c <= 8; c++) {
            getOrCreateCell(note1Row, c).setCellStyle(styles.finalJudgeStyle);
        }

        // 第29行
        Row note2Row = getOrCreateRow(sheet, 28);
        note2Row.setHeightInPoints(25);

        Cell note2Cell = getOrCreateCell(note2Row, 0);
        note2Cell.setCellValue("注2：未经本公司书面批准，不得部分复制本报告。");
        note2Cell.setCellStyle(styles.noteStyle);
        sheet.addMergedRegion(new CellRangeAddress(28, 28, 0, 4));
        for (int c = 1; c <= 4; c++) {
            getOrCreateCell(note2Row, c).setCellStyle(styles.noteStyle);
        }

        for (int c = 5; c <= 8; c++) {
            getOrCreateCell(note2Row, c).setCellStyle(styles.finalJudgeStyle);
        }

        return 29;
    }

    /**
     * 第30行：签名栏
     */
    /**
     * 第30行：签名栏
     */
    private static int createSignatureSection(Sheet sheet, int currentRow, ExcelStyles styles) {
        Row signRow = getOrCreateRow(sheet, currentRow);
        signRow.setHeightInPoints(25);

        // 检验
        Cell signCell1 = getOrCreateCell(signRow, 0);
        signCell1.setCellValue("     检验：");
        signCell1.setCellStyle(styles.leftStyle);
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, 2));
        getOrCreateCell(signRow, 1).setCellStyle(styles.leftStyle);
        getOrCreateCell(signRow, 2).setCellStyle(styles.leftStyle);

        // 签发
        Cell signCell2 = getOrCreateCell(signRow, 3);
        signCell2.setCellValue("     签发：");
        signCell2.setCellStyle(styles.leftStyle);
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 3, 5));
        getOrCreateCell(signRow, 4).setCellStyle(styles.leftStyle);
        getOrCreateCell(signRow, 5).setCellStyle(styles.leftStyle);

        // 日期
        Cell signCell3 = getOrCreateCell(signRow, 6);
        String dateText = "日期：" + new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
        signCell3.setCellValue(dateText);
        signCell3.setCellStyle(styles.normalStyle);
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 6, 8));
        getOrCreateCell(signRow, 7).setCellStyle(styles.normalStyle);
        getOrCreateCell(signRow, 8).setCellStyle(styles.normalStyle);

        return currentRow + 1;
    }

    // ==================== 辅助方法 ====================

    private static void createMergedCell(Sheet sheet, int firstRow, int lastRow,
                                         int firstCol, int lastCol, String text, CellStyle style) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
        Row row = getOrCreateRow(sheet, firstRow);
        Cell cell = getOrCreateCell(row, firstCol);
        cell.setCellValue(text);
        cell.setCellStyle(style);

        for (int r = firstRow; r <= lastRow; r++) {
            Row rr = getOrCreateRow(sheet, r);
            for (int c = firstCol; c <= lastCol; c++) {
                Cell cc = getOrCreateCell(rr, c);
                cc.setCellStyle(style);
            }
        }
    }

    private static void createInfoCell(Sheet sheet, int rowIdx, int startCol,
                                       String label, String value,
                                       CellStyle labelStyle, CellStyle valueStyle, int endCol) {
        Row row = getOrCreateRow(sheet, rowIdx);

        // 标签
        Cell labelCell = getOrCreateCell(row, startCol);
        labelCell.setCellValue(label);
        labelCell.setCellStyle(labelStyle);

        // 值
        Cell valueCell = getOrCreateCell(row, startCol + 1);
        valueCell.setCellValue(value);
        valueCell.setCellStyle(valueStyle);

        if (endCol > startCol + 1) {
            sheet.addMergedRegion(new CellRangeAddress(rowIdx, rowIdx, startCol + 1, endCol));
            for (int c = startCol + 2; c <= endCol; c++) {
                getOrCreateCell(row, c).setCellStyle(valueStyle);
            }
        }
    }

    private static Row getOrCreateRow(Sheet sheet, int rowIdx) {
        Row row = sheet.getRow(rowIdx);
        if (row == null) {
            row = sheet.createRow(rowIdx);
            row.setHeightInPoints(20);
        }
        return row;
    }

    private static Cell getOrCreateCell(Row row, int colIdx) {
        Cell cell = row.getCell(colIdx);
        if (cell == null) {
            cell = row.createCell(colIdx);
        }
        return cell;
    }

    /**
     * 读取classpath下的文件字节
     */
    public static byte[] readClasspathBytes(String classpathLocation) {
        try (InputStream is = new ClassPathResource(classpathLocation).getInputStream();
             ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            if (is == null) return null;
            byte[] buf = new byte[8192];
            int len;
            while ((len = is.read(buf)) != -1) {
                baos.write(buf, 0, len);
            }
            return baos.toByteArray();
        } catch (IOException e) {
            log.warn("读取classpath文件失败: {}", classpathLocation, e);
            return null;
        }
    }

    /**
     * 读取文件字节
     */
    public static byte[] readFileBytes(String path) {
        if (StringUtils.isBlank(path)) return null;
        File f = new File(path);
        if (!f.exists()) return null;
        try (FileInputStream fis = new FileInputStream(f);
             ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            byte[] buf = new byte[8192];
            int len;
            while ((len = fis.read(buf)) != -1) {
                baos.write(buf, 0, len);
            }
            return baos.toByteArray();
        } catch (IOException e) {
            log.warn("读取文件失败: {}", path, e);
            return null;
        }
    }

    // ==================== 内部类：字体集合 ====================

    static class ExcelFonts {
        Font companyFont;
        Font titleFont;
        Font subtitleFont;
        Font headerFont;
        Font judgementFont;
        Font noteFont;
        Font normalFont;
    }

    // ==================== 内部类：样式集合 ====================

    static class ExcelStyles {
        CellStyle companyStyle;
        CellStyle companyStyleLeft;
        CellStyle titleStyle;
        CellStyle subtitleStyle;
        CellStyle headerStyle;
        CellStyle headerStyleWrap;
        CellStyle judgementStyle;
        CellStyle noteStyle;
        CellStyle finalJudgeStyle;
        CellStyle normalStyle;
        CellStyle leftStyle;
        CellStyle wrapStyle;
        CellStyle verticalStyle;
    }
    /**
     * 第31行：底部留白行（合并A-I列，所有边框，行高50）
     */
    private static int createBottomBlankRow(Sheet sheet, int currentRow, ExcelStyles styles) {
        Row blankRow = getOrCreateRow(sheet, currentRow);
        blankRow.setHeightInPoints(50);

        // 合并A-I列
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, 8));

        // 应用带全边框的样式
        for (int c = 0; c <= 8; c++) {
            Cell cell = getOrCreateCell(blankRow, c);
            cell.setCellStyle(styles.normalStyle);
        }

        return currentRow + 1;
    }
}